﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyKTV
{
    
    
        public class DBHelper
        {
            private string connstring = "Data Source=127.0.0.1;Initial Catalog=MyKTV;User ID=sa;Password=123456@qwe";
            private SqlConnection connection;
            public SqlConnection Connection
            {
                get
                {
                    if (connection == null)
                    {
                        connection = new SqlConnection(connstring);
                    }
                    return connection;
                }
            }
            public void Openconnection()
            {
                if (Connection.State == ConnectionState.Closed)
                {
                    Connection.Open();
                }
                else if (Connection.State == ConnectionState.Broken)
                {
                    Connection.Close();
                    Connection.Open();
                }
            }
            public void closeAll(SqlConnection sqlConnection, SqlDataReader sqlDataReader)
            {
                try
                {
                    if (null != sqlDataReader)
                    {
                        sqlDataReader.Close();
                    }
                    if (null != sqlConnection)
                    {
                        sqlConnection.Close();
                    }

                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }


            /// <summary>
            /// 创建Connection对象并返回
            /// </summary>
            /// <returns></returns>
            public SqlConnection getConn()
            {
                SqlConnection conn = null;
                try
                {
                    string connString = null;
                    conn = new SqlConnection(connString);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
                return conn;
            }


            /// <summary>
            /// 查找数据库路径集并返回
            /// </summary>
            /// <param name="Lujin"></param>
            /// <returns></returns>
            public string updLJ(string Lujin)
            {
                SqlConnection connection = null;
                string sql = "select resource_path from resource_path where resource_type = '" + Lujin + "'";
                try
                {
                    //1、创建连接
                    connection = getConn();
                    //2、打开连接
                    connection.Open();
                    //3、创建command
                    SqlCommand sqlCommand = new SqlCommand(sql, connection);
                    //4、执行sql
                    Lujin = sqlCommand.ExecuteScalar().ToString();

                    return Lujin;

                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    return Lujin;
                }
                finally
                {
                    //关闭资源
                    closeAll(connection, null);
                }
            }
            public void Closeconnection()
            {
                if (Connection.State == ConnectionState.Open || Connection.State == ConnectionState.Broken)
                {
                    Connection.Close();
                }
            }

        }
    }

